package com.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import utiles.ConexionDAO;

import com.dto.datoAreaProdDTO;
import com.dto.datosRolUserDTO;
import com.dto.datosSystDTO;
import com.dto.datosVideoDTO;
import com.dto.rolesDTO;
import com.dto.videoUsuarioDTO;

public class videosDAO {
	
	public List getVideoByUser(String usuario) throws SQLException, IOException{
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();
		String min = "";
		String seg = "";
		String tduracion = "";		
		//System.out.println("usuarioVideos: "+usuario);	
		Statement stmt = conn.createStatement();
			try{
				ResultSet rs = stmt.executeQuery("{  call dbo.CapDif_BuscaVideosUsuario_Rol("+Integer.parseInt(usuario)+")}");
				while (rs.next()) {
					//System.out.println(">>>>>>>"+rs.getString(1));
					videoUsuarioDTO videodto = new videoUsuarioDTO();
					videodto.setIdVideo(rs.getString(1));
					videodto.setFuncion(rs.getString(2));
					videodto.setNombreArchivo(rs.getString(3));
					min = rs.getString(4);
					int mint = 0;
					mint = Integer.parseInt(min);
					if(mint<9){
						min = min;
					}
					seg = rs.getString(5);
					int sint = 0;
					sint = Integer.parseInt(min);
					if(sint<9){
						seg = seg;
					}				
					tduracion = min+":"+seg;							
					videodto.setTduracion(tduracion);
					videodto.setSistema(rs.getString(7));
					videodto.setSubsistema(rs.getString(10));
					videodto.setModulo(rs.getString(13));
					videodto.setCampana(rs.getString(19));
					videodto.setEstado(rs.getString(24));
					String rutaFTPSSM ="ftp://ftpflv:qwe321@10.10.119.110/"+rs.getString(8)+"/"+rs.getString(11)+"/"+rs.getString(14)+"/"+rs.getString(3);							
					String rutaFTPCEV ="ftp://ftpflv:qwe321@10.10.119.110/"+rs.getString(20)+"/"+rs.getString(17)+"/Virtual/"+rs.getString(3);					
					videodto.setRutaFTPCEV(rutaFTPCEV);
					videodto.setRutaFTPSSM(rutaFTPSSM);	
					videodto.setEstado(rs.getString(24));
					videodto.setUltlectura(rs.getString(25));
					videodto.setTvisto(rs.getString(23));
					//videodto.setRutaFTP(rutaFTP);
					listDatosVideos.add(videodto);	
					/*System.out.println("estado: "+rs.getString(24));
					System.out.println("tiempoVisto: "+rs.getString(23));
					System.out.println("UltimaLectura: "+rs.getString(25));*/
				}					
				rs.close();
				stmt.close();
			
					
			/*videoUsuarioDTO videodto = new videoUsuarioDTO();
			videodto.campana    = "001";
			videodto.estado     = "activo";
			videodto.funcion    = "primera";
			videodto.modulo     = "tercero";
			videodto.reproducir ="definir";
			videodto.sistema    = "siniestros";
			videodto.subsistema = "moviles";
			videodto.tduracion  = "5:00";
			videodto.tvisto     = "3:43";
			videodto.ultlectura = "12/04/2012";
			listDatosVideos.add(videodto);	*/
			//System.out.println("largo"+listDatosVideos.size());
			
		} catch (SQLException ex) {  

			System.out.println(ex.getMessage());
		} finally {
			connDao.desconecta(conn);
		}
		return listDatosVideos;	
	}
	
	public List getRolUsuario(String usuario) throws SQLException, IOException{
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();
		//String usuarioRut = "10062637";
		//System.out.println("usuarioRol: "+usuario);	
		Statement stmt = conn.createStatement();
		try{
			ResultSet rs = stmt.executeQuery("{  call RlsMst..LeerRolesAsignadosUsuario(9035,"+Integer.parseInt(usuario)+")}");
			while (rs.next()) {
				//System.out.println(">>>>>>>"+rs.getString(1));
				datosRolUserDTO videodto = new datosRolUserDTO();
				videodto.setIdentProd(rs.getString(1));
				videodto.setTitulo(rs.getString(2));
				videodto.setIdentRol(rs.getString(3));
				videodto.setNombre(rs.getString(4));
				videodto.setRutUser(rs.getString(5));
				videodto.setNombre1(rs.getString(6));
				videodto.setHablilitado(rs.getString(7));
				videodto.setRutUser1(rs.getString(8));
				listDatosVideos.add(videodto);	
				/*System.out.println("setIdentProd "+rs.getString(1));
				System.out.println("setTitulo "+rs.getString(2));
				System.out.println("setIdentRol "+rs.getString(3));
				System.out.println("setNombre "+rs.getString(4));
				System.out.println("setRutUser "+rs.getString(5));
				System.out.println("setNombre1 "+rs.getString(6));
				System.out.println("setHablilitado "+rs.getString(7));
				System.out.println("setRutUser1 "+rs.getString(8));*/
			}					
			rs.close();
			stmt.close();
		
	} catch (SQLException ex) {  

		System.out.println(ex.getMessage());
	} finally {
		connDao.desconecta(conn);
	}
	return listDatosVideos;
	}
	
	public  List getDatosArea() throws SQLException, IOException{		
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();	
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("{ ? =call RlsMst.dbo.LeerAreas() }");
			while (rs.next()) {				
				datoAreaProdDTO BuscaDTO = new datoAreaProdDTO();
				BuscaDTO.setIdentificador(rs.getString(1));
				BuscaDTO.setNombre(rs.getString(2));				
				//System.out.println(rs.getString(2));			
				listDatosVideos.add(BuscaDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  

			System.out.println(ex.getMessage());
		} finally {
			connDao.desconecta(conn);
		}
		return listDatosVideos;	
	}
	
	public  List getDatosProdIdArea(String idArea) throws SQLException, IOException{		
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();	
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("{ ? =call RlsMst.dbo.LeerProductoPorIdArea("+Integer.parseInt(idArea)+",0) }");
			while (rs.next()) {				
				datoAreaProdDTO BuscaDTO = new datoAreaProdDTO();
				BuscaDTO.setNombreArea(rs.getString(2));
				BuscaDTO.setIdentificador(rs.getString(3));
				BuscaDTO.setNombre(rs.getString(4));	
				//System.out.println(rs.getString(1));
				//System.out.println(rs.getString(2));	
				//System.out.println(rs.getString(3));
				//System.out.println(rs.getString(4));
				
				listDatosVideos.add(BuscaDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  

			System.out.println(ex.getMessage());
		} finally {
			connDao.desconecta(conn);
		}
		return listDatosVideos;	
	}
	
	public  List getDatosRoles(String idProd) throws SQLException, IOException{		
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();	
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("{ ? =call dbo.LeerRoles2("+Integer.parseInt(idProd)+") }");
			while (rs.next()) {				
				rolesDTO BuscaDTO = new rolesDTO();
				BuscaDTO.setIdentificador(rs.getString(2));
				BuscaDTO.setIdentificadorProducto(rs.getString(1));
				BuscaDTO.setNombre(rs.getString(3));
				BuscaDTO.setHabilitado(rs.getString(4));
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));	
				System.out.println(rs.getString(3));
				System.out.println(rs.getString(4));*/
				listDatosVideos.add(BuscaDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  

			System.out.println(ex.getMessage());
		} finally {
			connDao.desconecta(conn);
		}
		return listDatosVideos;	
	}
	
	public  List getDatosRolesByAccEvento(String idProd) throws SQLException, IOException{		
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();	
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("{ ? =call dbo.LeerRolesAccEvento("+Integer.parseInt(idProd)+") }");
			while (rs.next()) {				
				rolesDTO BuscaDTO = new rolesDTO();
				BuscaDTO.setIdentificador(rs.getString(2));
				BuscaDTO.setIdentificadorProducto(rs.getString(1));
				BuscaDTO.setNombre(rs.getString(3));
				BuscaDTO.setHabilitado(rs.getString(4));
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));	
				System.out.println(rs.getString(3));
				System.out.println(rs.getString(4));*/
				listDatosVideos.add(BuscaDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  

			System.out.println(ex.getMessage());
		} finally {
			connDao.desconecta(conn);
		}
		return listDatosVideos;	
	}
	
	public  List getDatosRolesByAccGrupal(String idProd) throws SQLException, IOException{		
		ConexionDAO connDao = new ConexionDAO();
		Connection conn = connDao.getConnection();
		List listDatosVideos = new ArrayList();	
		try{
			Statement stmt = conn.createStatement();
			//System.out.println("idProd: "+idProd);
			ResultSet rs = stmt.executeQuery("{ ? =call CapDef.dbo.LeerRolesAccGrupal("+Integer.parseInt(idProd)+") }");
			while (rs.next()) {				
				rolesDTO BuscaDTO = new rolesDTO();
				BuscaDTO.setIdentificador(rs.getString(2));
				BuscaDTO.setIdentificadorProducto(rs.getString(1));
				BuscaDTO.setNombre(rs.getString(3));
				BuscaDTO.setHabilitado(rs.getString(4));
				/*System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));	
				System.out.println(rs.getString(3));
				System.out.println(rs.getString(4));
				System.out.println(rs.getString(5));
				System.out.println(rs.getString(6));*/
				listDatosVideos.add(BuscaDTO);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  

			System.out.println(ex.getMessage());
		} finally {
			connDao.desconecta(conn);
		}
		return listDatosVideos;	
	}
	
	
	
	public static void main(String[] args) {
		
		// TODO Auto-generated method stub
		videosDAO isnt = new videosDAO();
		try {
			isnt.getDatosRoles("9030");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
